SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO








CREATE PROCEDURE [dbo].[usp_ShowSurveyResults]
	-- Add the parameters for the stored procedure here
(
	@param_survey INT
)

AS
	CREATE TABLE #temp_table
	(
	item INT,
	question NVARCHAR(200),
	option1 FLOAT,
	option2 FLOAT,
	option3 FLOAT,
	option4 FLOAT,
	option5 FLOAT,
	option6 FLOAT
	)
	Declare cur Cursor for 
	SELECT item,question,selected 
	FROM dbo.JOL_SurveyManagement_Respond,dbo.JOL_SurveyManagement_Items 
	WHERE item=dbo.JOL_SurveyManagement_Items.id  AND survey=@param_survey
	
	
	DECLARE @temp_item INT
	DECLARE @temp_question NVARCHAR(200)
	DECLARE @temp_selected NVARCHAR(50)
	
	SET @temp_item=0
	SET @temp_question=''
	SET @temp_selected=''
	
	Open cur
	Fetch Next From cur Into @temp_item ,@temp_question,@temp_selected
	While (@@Fetch_Status=0 ) 
	BEGIN
		DECLARE @temp_option1 FLOAT
		DECLARE @temp_option2 FLOAT
		DECLARE @temp_option3 FLOAT
		DECLARE @temp_option4 FLOAT
		DECLARE @temp_option5 FLOAT
		DECLARE @temp_option6 FLOAT
		SET @temp_option1=0
		SET @temp_option2=0
		SET @temp_option3=0
		SET @temp_option4=0
		SET @temp_option5=0
		SET @temp_option6=0
			
		DECLARE @i int
		Declare @Start int
		Declare @Length int
		Declare @TotalLenght int
		Set @i = 1;
		Set @Start = 1;
		Set @Length = 1;
		Set @TotalLenght = LEN(@temp_selected);
		WHILE @i<=@TotalLenght
		Begin
			
			IF SUBSTRING(@temp_selected,@Start,@Length)='1'
			BEGIN
				SET @temp_option1=1
			END
			ELSE
				IF SUBSTRING(@temp_selected,@Start,@Length)='2'
				BEGIN
					SET @temp_option2=1
				END
				ELSE
					IF SUBSTRING(@temp_selected,@Start,@Length)='3'
					BEGIN
						SET @temp_option3=1
					END
					ELSE
						IF SUBSTRING(@temp_selected,@Start,@Length)='4'
						BEGIN
							SET @temp_option4=1
						END
						ELSE
							IF SUBSTRING(@temp_selected,@Start,@Length)='5'
							BEGIN
								SET @temp_option5=1
							END
							ELSE
								IF SUBSTRING(@temp_selected,@Start,@Length)='6'
								BEGIN
									SET @temp_option6=1
								END
		
			Set @i = @i + 1;
			SET @Start=@Start+1
		End
		
		INSERT INTO #temp_table VALUES(@temp_item,@temp_question,@temp_option1,@temp_option2,@temp_option3,@temp_option4,@temp_option5,@temp_option6)
			
		FETCH Next From cur into @temp_item ,@temp_question,@temp_selected
		
	END
	CLOSE cur
    DEALLOCATE cur
    
    SELECT 
    item,
    question,
    SUM(option1)/(SUM(option1)+SUM(option2)+SUM(option3)+SUM(option4)+SUM(option5)+SUM(option6)) AS option1,
    SUM(option2)/(SUM(option1)+SUM(option2)+SUM(option3)+SUM(option4)+SUM(option5)+SUM(option6)) AS option2,
    SUM(option3)/(SUM(option1)+SUM(option2)+SUM(option3)+SUM(option4)+SUM(option5)+SUM(option6)) AS option3,
    SUM(option4)/(SUM(option1)+SUM(option2)+SUM(option3)+SUM(option4)+SUM(option5)+SUM(option6)) AS option4,
    SUM(option5)/(SUM(option1)+SUM(option2)+SUM(option3)+SUM(option4)+SUM(option5)+SUM(option6)) AS option5,
    SUM(option6)/(SUM(option1)+SUM(option2)+SUM(option3)+SUM(option4)+SUM(option5)+SUM(option6)) AS option6
    FROM #temp_table 
    GROUP BY item,question
   
	

	 









GO
